package com.xingyi.bacs.dao;

import com.xingyi.bacs.bean.TbAccountInfo;
import com.xingyi.bacs.util.JDBCUtil;
import com.xingyi.bacs.util.LogUtil;
import com.xingyi.bacs.util.StringUtil;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by wujt on 8/9/2022
 */
@Repository
public class ExcelToOracleDaoImp implements ExcelToOracleDao{
    @Override
    public void dealExcel(List<String> columnList, List<String> columnTypeList, List<Object[]> list, String tableName, String seqName) {
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstat = null;
        Object[] obj = null;
        Object[] objType = new Object[columnTypeList.size()];
        try {
            StringBuffer insertColumn = new StringBuffer("id,");
            for (String str : columnList){
                insertColumn.append(str+",");
            }
            insertColumn.deleteCharAt(insertColumn.length()-1);
            StringBuffer questionMark = new StringBuffer(seqName+".NEXTVAL,");
            for (int i=0; i<columnList.size(); i++){
                questionMark.append("?,");
            }
            questionMark.deleteCharAt(questionMark.length()-1);
            for (int i=0; i<columnTypeList.size(); i++){
                objType[i] = columnTypeList.get(i);
            }
            String sql = "insert into "+tableName+" ("+insertColumn+") values ("+questionMark+")";

            conn = JDBCUtil.getQSConn();
            pstat = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            for(int i=0;i<list.size();i++){
                obj = list.get(i);
                for (int j=1;j<=obj.length;j++){
                    if ("VARCHAR2".equals(getValue(objType[j-1]))){
                        pstat.setString(j,getValue(obj[j-1]));
                    }else if ("INTEGER".equals(getValue(objType[j-1]))){
                        pstat.setInt(j,Integer.parseInt(getValue(obj[j-1])));
                    }else if ("NUMBER".equals(getValue(objType[j-1]))){
                        pstat.setBigDecimal(j,new BigDecimal(getValue(obj[j-1])));
                    }else{
                        pstat.setObject(j,obj[j-1]);
                    }
                }
                pstat.addBatch();
                if (i%2000==0&&i>0) {
                    pstat.executeBatch();
                    conn.commit();
                    LogUtil.info(this.getClass(), "批量插入信息表【"+tableName+"】分批保存条数至："+i);
                }
            }
            pstat.executeBatch();
            conn.commit();
            long endTime = System.currentTimeMillis();
            LogUtil.info(this.getClass(), "同步结算信息表【"+tableName+"】分批保存条数至："+list.size()+"，耗时："+(endTime-startTime)/1000);
        } catch (Exception e) {
            e.printStackTrace();
            if(conn!=null){
                try {
                    conn.rollback();
                    LogUtil.info(this.getClass(), "同步结算信息表【"+tableName+"】分批次保存异常数据进行回滚！");
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            throw new RuntimeException(e.getMessage());
        } finally {
            JDBCUtil.getClose(conn, pstat, null);
        }
    }

    private String getValue(Object obj){
        if (obj==null){
            return "";
        }
        return obj.toString();
    }

}
